// schedule.js
const { runAsync, allAsync, getAsync, retryOperation, log } = require('./dbConfig');

/**
 * 新增档期
 * @param {Object} req - 请求对象，包含档期信息
 * @param {Object} res - 响应对象
 */
const addSchedule = async (req, res) => {
  const { schedule_type, schedule_date, company_type, partner_company_id, company_name, hotel_name, ceremony_hall_id, ceremony_hall_name, ceremony_hall_remarks, fixed_gear_id, fixed_gear_name, is_recommend, recommend_user, rebate, service_price, deposit, studio_settlement, profit, wedding_is_checkout, studio_is_checkout, archive_img, is_postponement, is_cancel, remarks, create_user } = req.body;
  if (!schedule_type || !schedule_date) {
    return res.status(500).json({ success: false, msg: '档期类型和日期为必填项', code: '1', data: [] });
  }

  try {
    await retryOperation(() =>
      runAsync(
        `INSERT INTO sk_schedule (schedule_type, schedule_date, company_type, partner_company_id, company_name, hotel_name, ceremony_hall_id, ceremony_hall_name, ceremony_hall_remarks, fixed_gear_id, fixed_gear_name, is_recommend, recommend_user, rebate, service_price, deposit, studio_settlement, profit, wedding_is_checkout, studio_is_checkout, archive_img, is_postponement, is_cancel, remarks, create_user) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [schedule_type, schedule_date, company_type, partner_company_id, company_name, hotel_name, ceremony_hall_id, ceremony_hall_name, ceremony_hall_remarks, fixed_gear_id, fixed_gear_name, is_recommend, recommend_user, rebate, service_price, deposit, studio_settlement, profit, wedding_is_checkout, studio_is_checkout, archive_img, is_postponement, is_cancel, remarks, create_user]
      )
    );
    const result = await getAsync(`SELECT last_insert_rowid() as id`);
    const lastID = result.id;
    log(`Inserted ID: ${lastID}`);
    res.json({ success: true, msg: '新增成功', code: '0', data: [lastID] });
  } catch (err) {
    log(`新增档期失败: ${err.message}`);
    res.status(500).json({ success: false, msg: '新增档期信息失败，请稍后再试', code: '1', data: [] });
  }
};

/**
 * 修改档期信息
 * @param {Object} req - 请求对象，包含更新信息和 ID
 * @param {Object} res - 响应对象
 */
const updateSchedule = async (req, res) => {
  const { id, schedule_type, schedule_date, company_type, partner_company_id, company_name, hotel_name, ceremony_hall_id, ceremony_hall_name, ceremony_hall_remarks, fixed_gear_id, fixed_gear_name, is_recommend, recommend_user, rebate, service_price, deposit, studio_settlement, profit, wedding_is_checkout, studio_is_checkout, archive_img, is_postponement, is_cancel, remarks, update_user } = req.body;

  try {
    await retryOperation(() =>
      runAsync(
        `UPDATE sk_schedule SET schedule_type = ?, schedule_date = ?, company_type = ?, partner_company_id = ?, company_name = ?, hotel_name = ?, ceremony_hall_id = ?, ceremony_hall_name = ?, ceremony_hall_remarks = ?, fixed_gear_id = ?, fixed_gear_name = ?, is_recommend = ?, recommend_user = ?, rebate = ?, service_price = ?, deposit = ?, studio_settlement = ?, profit = ?, wedding_is_checkout = ?, studio_is_checkout = ?, archive_img = ?, is_postponement = ?, is_cancel = ?, remarks = ?, update_user = ?, update_time = CURRENT_TIMESTAMP WHERE id = ? AND del_flag = '0'`,
        [schedule_type, schedule_date, company_type, partner_company_id, company_name, hotel_name, ceremony_hall_id, ceremony_hall_name, ceremony_hall_remarks, fixed_gear_id, fixed_gear_name, is_recommend, recommend_user, rebate, service_price, deposit, studio_settlement, profit, wedding_is_checkout, studio_is_checkout, archive_img, is_postponement, is_cancel, remarks, update_user, id]
      )
    );
    res.json({ success: true, msg: '修改成功', code: '0', data: [] });
  } catch (err) {
    log(`修改档期失败: ${err.message}`);
    res.status(500).json({ success: false, msg: '修改档期信息失败，请稍后再试', code: '1', data: [] });
  }
};

/**
 * 删除档期（逻辑删除）
 * @param {Object} req - 请求对象，包含 ID
 * @param {Object} res - 响应对象
 */
const deleteSchedule = async (req, res) => {
  const { id } = req.params;

  try {
    await retryOperation(() =>
      runAsync(`UPDATE sk_schedule SET del_flag = '1' WHERE id = ?`, [id])
    );
    res.json({ success: true, msg: '删除成功', code: '0', data: [] });
  } catch (err) {
    log(`删除档期失败: ${err.message}`);
    res.status(500).json({ success: false, msg: '删除档期信息失败，请稍后再试', code: '1', data: [] });
  }
};

/**
 * 批量删除档期（逻辑删除）
 * POST /schedule/batch-delete
 * Body: ["1", "2", "3"]
 */
const batchDeleteSchedule = async (req, res) => {
  const { ids } = req.body;

  // 校验参数
  if (!Array.isArray(ids) || ids.length === 0) {
    return res.status(500).json({
      success: false,
      msg: '删除失败，请求参数异常',
      code: '1',
      data: []
    });
  }

  // 转换为数字数组并过滤无效值
  const validIds = ids
    .map(id => parseInt(id))
    .filter(id => !isNaN(id) && id > 0);

  if (validIds.length === 0) {
    return res.status(500).json({
      success: false,
      msg: '未找到有效的 ID',
      code: '1',
      data: []
    });
  }

  try {
    // 构建 IN 子句占位符: (?, ?, ?)
    const placeholders = validIds.map(() => '?').join(', ');
    const query = `UPDATE sk_schedule SET del_flag = '1' WHERE id IN (${placeholders}) AND del_flag = '0'`;

    const result = await retryOperation(() =>
      runAsync(query, validIds)
    );

    const affectedRows = result.changes || 0;

    log(`批量删除档期成功，影响行数: ${affectedRows}, IDs: [${validIds.join(', ')}]`);

    res.json({
      success: true,
      msg: '批量删除成功',
      code: '0',
      data: { deletedCount: affectedRows }
    });
  } catch (err) {
    log(`批量删除档期失败: ${err.message}`);
    res.status(500).json({
      success: false,
      msg: '批量删除失败，请稍后再试',
      code: '1',
      data: []
    });
  }
};

/**
 * 查询档期列表（带搜索条件和分页）
 * @param {Object} req - 请求对象，包含搜索条件和分页参数
 * @param {Object} res - 响应对象
 */
const getPageScheduleList = async (req, res) => {
  const { schedule_type, schedule_date, company_type, partner_company_id, company_name, hotel_name, ceremony_hall_id, ceremony_hall_name, fixed_gear_id, fixed_gear_name, is_recommend, recommend_user, wedding_is_checkout, studio_is_checkout, start_date, end_date, checkout_status, page, size } = req.query;

  const pageNum = parseInt(page) || 1; // 默认第 1 页
  const pageSize = parseInt(size) || 10; // 默认每页 10 条

  let query = 'SELECT *,CASE WHEN wedding_is_checkout = \'0\' AND studio_is_checkout = \'0\' THEN 0 ELSE 1 END AS checkout_status FROM sk_schedule WHERE del_flag = \'0\'';
  let countQuery = 'SELECT COUNT(*) as total FROM sk_schedule WHERE del_flag = \'0\'';
  const params = [];
  const countParams = [];

  if (schedule_type) {
    query += ' AND schedule_type = ?';
    countQuery += ' AND schedule_type = ?';
    params.push(schedule_type);
    countParams.push(schedule_type);
  }

  if (schedule_date) {
    query += ' AND schedule_date LIKE ?';
    countQuery += ' AND schedule_date LIKE ?';
    params.push(`%${schedule_date}%`);
    countParams.push(`%${schedule_date}%`);
  }

  if (company_type) {
    query += ' AND company_type = ?';
    countQuery += ' AND company_type = ?';
    params.push(company_type);
    countParams.push(company_type);
  }

  if (partner_company_id) {
    query += ' AND partner_company_id = ?';
    countQuery += ' AND partner_company_id = ?';
    params.push(partner_company_id);
    countParams.push(partner_company_id);
  }

  if (company_name) {
    query += ' AND company_name LIKE ?';
    countQuery += ' AND company_name LIKE ?';
    params.push(`%${company_name}%`);
    countParams.push(`%${company_name}%`);
  }

  if (hotel_name) {
    query += ' AND hotel_name LIKE ?';
    countQuery += ' AND hotel_name LIKE ?';
    params.push(`%${hotel_name}%`);
    countParams.push(`%${hotel_name}%`);
  }

  if (ceremony_hall_id) {
    query += ' AND ceremony_hall_id = ?';
    countQuery += ' AND ceremony_hall_id = ?';
    params.push(ceremony_hall_id);
    countParams.push(ceremony_hall_id);
  }

  if (ceremony_hall_name) {
    query += ' AND ceremony_hall_name LIKE ?';
    countQuery += ' AND ceremony_hall_name LIKE ?';
    params.push(`%${ceremony_hall_name}%`);
    countParams.push(`%${ceremony_hall_name}%`);
  }

  if (fixed_gear_id) {
    query += ' AND fixed_gear_id = ?';
    countQuery += ' AND fixed_gear_id = ?';
    params.push(fixed_gear_id);
    countParams.push(fixed_gear_id);
  }

  if (fixed_gear_name) {
    query += ' AND fixed_gear_name LIKE ?';
    countQuery += ' AND fixed_gear_name LIKE ?';
    params.push(`%${fixed_gear_name}%`);
    countParams.push(`%${fixed_gear_name}%`);
  }

  if (is_recommend) {
    query += ' AND is_recommend = ?';
    countQuery += ' AND is_recommend = ?';
    params.push(is_recommend);
    countParams.push(is_recommend);
  }

  if (recommend_user) {
    query += ' AND recommend_user LIKE ?';
    countQuery += ' AND recommend_user LIKE ?';
    params.push(`%${recommend_user}%`);
    countParams.push(`%${recommend_user}%`);
  }

  if (wedding_is_checkout) {
    query += ' AND wedding_is_checkout = ?';
    countQuery += ' AND wedding_is_checkout = ?';
    params.push(wedding_is_checkout);
    countParams.push(wedding_is_checkout);
  }

  if (studio_is_checkout) {
    query += ' AND studio_is_checkout = ?';
    countQuery += ' AND studio_is_checkout = ?';
    params.push(studio_is_checkout);
    countParams.push(studio_is_checkout);
  }

  // 添加时间段过滤
  if (start_date && end_date) {
    query += ' AND schedule_date BETWEEN ? AND ?';
    countQuery += ' AND schedule_date BETWEEN ? AND ?';
    params.push(start_date, end_date);
    countParams.push(start_date, end_date);
  } else if (start_date) {
    query += ' AND schedule_date >= ?';
    countQuery += ' AND schedule_date >= ?';
    params.push(start_date);
    countParams.push(start_date);
  } else if (end_date) {
    query += ' AND schedule_date <= ?';
    countQuery += ' AND schedule_date <= ?';
    params.push(end_date);
    countParams.push(end_date);
  }

  // 添加结账状态过滤
  if (checkout_status !== undefined) {
    if (checkout_status === '0') {
      query += ' AND wedding_is_checkout = \'0\' AND studio_is_checkout = \'0\'';
      countQuery += ' AND wedding_is_checkout = \'0\' AND studio_is_checkout = \'0\'';
    } else if (checkout_status === '1') {
      query += ' AND (wedding_is_checkout = \'1\' OR studio_is_checkout = \'1\')';
      countQuery += ' AND (wedding_is_checkout = \'1\' OR studio_is_checkout = \'1\')';
    }
  }

  // // 动态排序：未结账按 id 升序，已结账按 id 降序
  // query += ' ORDER BY CASE WHEN wedding_is_checkout = \'1\' OR studio_is_checkout = \'1\' THEN id END ASC, CASE WHEN wedding_is_checkout = \'0\' AND studio_is_checkout = \'0\' THEN id END DESC LIMIT ? OFFSET ?';
  // 动态排序：先按 create_time 倒序，hotel_name 升序分组，再按未结账 id 升序、已结账 id 倒序
  query += ' ORDER BY schedule_date DESC, hotel_name ASC, CASE WHEN wedding_is_checkout = \'1\' OR studio_is_checkout = \'1\' THEN id END DESC, CASE WHEN wedding_is_checkout = \'0\' AND studio_is_checkout = \'0\' THEN id END ASC LIMIT ? OFFSET ?';
  params.push(pageSize, (pageNum - 1) * pageSize);

  try {
    const rows = await allAsync(query, params);
    const countRow = await getAsync(countQuery, countParams);
    res.json({ data: rows, code: '0', success: true, msg: '获取列表成功', total: countRow.total });
  } catch (err) {
    log(`Get schedule list error: ${err.message}`);
    res.status(500).json({ error: err.message });
  }
};

/**
 * 查询档期列表（带搜索条件不分页）
 * @param {Object} req - 请求对象，包含搜索条件
 * @param {Object} res - 响应对象
 */
const getScheduleList = async (req, res) => {
  const { schedule_type, schedule_date, company_type, partner_company_id, company_name, hotel_name, ceremony_hall_id, ceremony_hall_name, fixed_gear_id, fixed_gear_name, is_recommend, recommend_user, wedding_is_checkout, studio_is_checkout, start_date, end_date, checkout_status } = req.query;

  let query = 'SELECT *, CASE WHEN wedding_is_checkout = \'0\' AND studio_is_checkout = \'0\' THEN 0 ELSE 1 END AS checkout_status FROM sk_schedule WHERE del_flag = \'0\'';
  let countQuery = 'SELECT COUNT(*) as total FROM sk_schedule WHERE del_flag = \'0\'';
  const params = [];
  const countParams = [];

  if (schedule_type) {
    query += ' AND schedule_type = ?';
    countQuery += ' AND schedule_type = ?';
    params.push(schedule_type);
    countParams.push(schedule_type);
  }

  if (schedule_date) {
    query += ' AND schedule_date LIKE ?';
    countQuery += ' AND schedule_date LIKE ?';
    params.push(`%${schedule_date}%`);
    countParams.push(`%${schedule_date}%`);
  }

  if (company_type) {
    query += ' AND company_type = ?';
    countQuery += ' AND company_type = ?';
    params.push(company_type);
    countParams.push(company_type);
  }

  if (partner_company_id) {
    query += ' AND partner_company_id = ?';
    countQuery += ' AND partner_company_id = ?';
    params.push(partner_company_id);
    countParams.push(partner_company_id);
  }

  if (company_name) {
    query += ' AND company_name LIKE ?';
    countQuery += ' AND company_name LIKE ?';
    params.push(`%${company_name}%`);
    countParams.push(`%${company_name}%`);
  }

  if (hotel_name) {
    query += ' AND hotel_name LIKE ?';
    countQuery += ' AND hotel_name LIKE ?';
    params.push(`%${hotel_name}%`);
    countParams.push(`%${hotel_name}%`);
  }

  if (ceremony_hall_id) {
    query += ' AND ceremony_hall_id = ?';
    countQuery += ' AND ceremony_hall_id = ?';
    params.push(ceremony_hall_id);
    countParams.push(ceremony_hall_id);
  }

  if (ceremony_hall_name) {
    query += ' AND ceremony_hall_name LIKE ?';
    countQuery += ' AND ceremony_hall_name LIKE ?';
    params.push(`%${ceremony_hall_name}%`);
    countParams.push(`%${ceremony_hall_name}%`);
  }

  if (fixed_gear_id) {
    query += ' AND fixed_gear_id = ?';
    countQuery += ' AND fixed_gear_id = ?';
    params.push(fixed_gear_id);
    countParams.push(fixed_gear_id);
  }

  if (fixed_gear_name) {
    query += ' AND fixed_gear_name LIKE ?';
    countQuery += ' AND fixed_gear_name LIKE ?';
    params.push(`%${fixed_gear_name}%`);
    countParams.push(`%${fixed_gear_name}%`);
  }

  if (is_recommend) {
    query += ' AND is_recommend = ?';
    countQuery += ' AND is_recommend = ?';
    params.push(is_recommend);
    countParams.push(is_recommend);
  }

  if (recommend_user) {
    query += ' AND recommend_user LIKE ?';
    countQuery += ' AND recommend_user LIKE ?';
    params.push(`%${recommend_user}%`);
    countParams.push(`%${recommend_user}%`);
  }

  if (wedding_is_checkout) {
    query += ' AND wedding_is_checkout = ?';
    countQuery += ' AND wedding_is_checkout = ?';
    params.push(wedding_is_checkout);
    countParams.push(wedding_is_checkout);
  }

  if (studio_is_checkout) {
    query += ' AND studio_is_checkout = ?';
    countQuery += ' AND studio_is_checkout = ?';
    params.push(studio_is_checkout);
    countParams.push(studio_is_checkout);
  }

  // 添加时间段过滤
  if (start_date && end_date) {
    query += ' AND schedule_date BETWEEN ? AND ?';
    countQuery += ' AND schedule_date BETWEEN ? AND ?';
    params.push(start_date, end_date);
    countParams.push(start_date, end_date);
  } else if (start_date) {
    query += ' AND schedule_date >= ?';
    countQuery += ' AND schedule_date >= ?';
    params.push(start_date);
    countParams.push(start_date);
  } else if (end_date) {
    query += ' AND schedule_date <= ?';
    countQuery += ' AND schedule_date <= ?';
    params.push(end_date);
    countParams.push(end_date);
  }

  // 添加结账状态过滤
  if (checkout_status !== undefined) {
    if (checkout_status === '0') {
      query += ' AND wedding_is_checkout = \'0\' AND studio_is_checkout = \'0\'';
      countQuery += ' AND wedding_is_checkout = \'0\' AND studio_is_checkout = \'0\'';
    } else if (checkout_status === '1') {
      query += ' AND (wedding_is_checkout = \'1\' OR studio_is_checkout = \'1\')';
      countQuery += ' AND (wedding_is_checkout = \'1\' OR studio_is_checkout = \'1\')';
    }
  }

  // // 动态排序：未结账按 id 升序，已结账按 id 降序
  // query += ' ORDER BY CASE WHEN wedding_is_checkout = \'1\' OR studio_is_checkout = \'1\' THEN id END ASC, CASE WHEN wedding_is_checkout = \'0\' AND studio_is_checkout = \'0\' THEN id END DESC';

  // 动态排序：先按 create_time 倒序，hotel_name 升序分组，再按未结账 id 升序、已结账 id 倒序
  query += ' ORDER BY schedule_date DESC, hotel_name ASC, CASE WHEN wedding_is_checkout = \'1\' OR studio_is_checkout = \'1\' THEN id END DESC, CASE WHEN wedding_is_checkout = \'0\' AND studio_is_checkout = \'0\' THEN id END ASC';
  try {
    const rows = await allAsync(query, params);
    const countRow = await getAsync(countQuery, countParams);
    res.json({ data: rows, code: '0', success: true, msg: '获取列表成功', total: countRow.total });
  } catch (err) {
    log(`获取档期信息列表失败: ${err.message}`);
    res.status(500).json({ success: false, msg: '获取档期列表失败，请刷新重试', code: '1', data: [] });
  }
};

/**
 * 查询档期详情
 * @param {Object} req - 请求对象，包含 ID
 * @param {Object} res - 响应对象
 */
const queryScheduleDetail = async (req, res) => {
  const { id } = req.params;

  try {
    const row = await getAsync('SELECT * FROM sk_schedule WHERE id = ? AND del_flag = \'0\'', [id]);
    if (!row) {
      return res.status(500).json({ success: false, msg: '未查询到档期信息', code: '1', data: [] });
    }
    res.json({ data: row, code: '0', success: true, msg: '查询详情成功' });
  } catch (err) {
    log(`Get schedule detail error: ${err.message}`);
    res.status(500).json({ error: err.message });
  }
};

/**
 * 结账接口（修改 wedding_is_checkout 和 studio_is_checkout）
 * @param {Object} req - 请求对象，包含 ID 和结账状态
 * @param {Object} res - 响应对象
 */
const checkoutSchedule = async (req, res) => {
  const { id, wedding_is_checkout, studio_is_checkout, update_user } = req.body;
  if (wedding_is_checkout === undefined || studio_is_checkout === undefined) {
    return res.status(500).json({ success: false, msg: '结账状态为必填项', code: '1', data: [] });
  }

  try {
    await retryOperation(() =>
      runAsync(
        `UPDATE sk_schedule SET wedding_is_checkout = ?, studio_is_checkout = ?, update_user = ?, update_time = CURRENT_TIMESTAMP WHERE id = ? AND del_flag = '0'`,
        [wedding_is_checkout, studio_is_checkout, update_user, id]
      )
    );
    res.json({ success: true, msg: '结账成功', code: '0', data: [] });
  } catch (err) {
    log(`结账档期失败: ${err.message}`);
    res.status(500).json({ success: false, msg: '结账失败，请稍后再试', code: '1', data: [] });
  }
};

// 模块导出
module.exports = { addSchedule, updateSchedule, deleteSchedule, getPageScheduleList, getScheduleList, queryScheduleDetail, checkoutSchedule, batchDeleteSchedule };